# -*- coding: utf-8 -*-
import uuid
from collections import defaultdict
from typing import List, Optional, Dict

from sqlalchemy import desc

from db.session import with_session
from db.models.user import Group, UserGroup
from db.models.clause import ClauseStandard, ClauseFile, ClauseCompareHistory, ClauseGroups


""" ================================== ClauseStandard ======================================"""


# 获取所有标准条款的条款内容
@with_session
def db_get_all_standard_clauses(session):
    return session.query(ClauseStandard)\
        .filter(ClauseStandard.is_delete.is_(False))\
        .all()


# 获取指定 具体条款
@with_session
def db_get_standard_by_clauseId(session, clause_id):
    if isinstance(clause_id, str):
        clause_id = uuid.UUID(clause_id)
    return session.query(ClauseStandard)\
        .filter(ClauseStandard.id == clause_id, ClauseStandard.is_delete.is_(False)) \
        .first()


# 获取指定条款文件的所有条款内容
@with_session
def db_get_standards_by_fileId(session, file_id):
    if isinstance(file_id, str):
        file_id = uuid.UUID(file_id)
    return session.query(ClauseStandard)\
        .filter(ClauseStandard.file_id == file_id, ClauseStandard.is_delete.is_(False)) \
        .order_by(ClauseStandard.line_number) \
        .all()


# 获取所有符合筛选条件的条款文件内容
@with_session
def db_get_standards_by_filter(session, user_id, file_id):
    return session.query(ClauseStandard) \
        .join(ClauseGroups, ClauseGroups.clause == ClauseStandard.file_id) \
        .join(Group, ClauseGroups.group == Group.id) \
        .join(UserGroup, UserGroup.group == Group.id) \
        .filter(ClauseStandard.is_delete.is_(False), UserGroup.user == user_id, ClauseStandard.file_id == file_id) \
        .order_by(ClauseStandard.line_number) \
        .all()


@with_session
def db_get_clause_for_user(session, clause_id: int, user_id: int) -> Optional[ClauseStandard]:
    """管理员获取有权限的条款"""
    return session.query(ClauseStandard) \
            .join(ClauseGroups, ClauseGroups.clause == ClauseStandard.file_id) \
            .join(Group, ClauseGroups.group == Group.id) \
            .join(UserGroup, UserGroup.group == Group.id) \
            .filter(ClauseStandard.id == clause_id, ClauseStandard.is_delete == False, UserGroup.user == user_id)\
            .first()


# 根据条件检索返回条款文件的条款内容
@with_session
def db_get_clauses_by_filters(
        session,
        user_groups: List[int],
        is_superadmin: bool,
        clause_name: Optional[str] = None,
        institution: Optional[str] = None,
        clause_number: Optional[str] = None,
        original_text: Optional[str] = None,
        artificial_text: Optional[str] = None,
        area: Optional[str] = None,
        cargo: Optional[str] = None,
        page: int = 1,
        size: int = 100
) -> tuple[int, list[dict]]:
    """
    根据条件查询条款列表
    :return: (总数, 条款字典列表)
    """
    # 基础查询
    query = session.query(ClauseStandard).filter(ClauseStandard.is_delete.is_(False))

    # 权限过滤
    if not is_superadmin:
        query = query.join(ClauseGroups, ClauseGroups.clause == ClauseStandard.file_id) \
            .filter(ClauseGroups.group.in_(user_groups))

    # 添加过滤条件
    filters = {
        "clause_name": clause_name,
        "institution": institution,
        "clause_number": clause_number,
        "original_text": original_text,
        "artificial_text": artificial_text,
        "area": area,
        "cargo": cargo
    }

    # 模糊查询上述字段
    for field, value in filters.items():
        if value:
            query = query.filter(getattr(ClauseStandard, field).ilike(f"%{value}%"))

    # 分页
    total = query.count()
    clauses = query.offset((page - 1) * size).limit(size).all()

    return total, [clause.to_json() for clause in clauses]


# 获取所有条款名称和关联组
@with_session
def db_get_superadmin_clause_names(session) -> List[Dict]:
    """超级管理员获取所有条款名称及关联组"""
    query = session.query(ClauseStandard.clause_name, ClauseFile.id, Group.id, Group.name)\
        .join(ClauseFile, ClauseStandard.file_id == ClauseFile.id)\
        .join(ClauseGroups, ClauseGroups.clause == ClauseFile.id)\
        .join(Group, ClauseGroups.group == Group.id)\
        .filter(ClauseStandard.is_delete.is_(False), ClauseFile.is_delete.is_(False))\
        .distinct()\
        .all()

    result = {}
    for clause_name, file_id, g_id, g_name in query:
        key = f"{clause_name}_{file_id}"
        if key not in result:
            result[key] = {
                "clause_name": clause_name,
                "file_id": str(file_id),
                "groups": {g_id: {"id": g_id, "name": g_name}}
            }
        else:
            result[key]["groups"][g_id] = {"id": g_id, "name": g_name}

    return list(result.values())


# 管理员获取有权限的条款名称及完整组信息
@with_session
def db_get_admin_clause_names(session, user_id: int) -> List[Dict]:
    """管理员获取有权限的条款名称及完整组信息"""
    # 第一步：获取用户能访问的条款和部分组信息
    query = session.query(ClauseStandard.clause_name, ClauseFile.id, Group.id, Group.name)\
        .join(ClauseFile, ClauseStandard.file_id == ClauseFile.id)\
        .join(ClauseGroups, ClauseGroups.clause == ClauseFile.id)\
        .join(Group, ClauseGroups.group == Group.id)\
        .join(UserGroup, UserGroup.group == Group.id) \
        .filter(ClauseStandard.is_delete.is_(False), ClauseFile.is_delete.is_(False), UserGroup.user == user_id)\
        .all()

    # 处理查询结果
    result = {}
    file_ids = set()
    for clause_name, file_id, g_id, g_name in query:
        file_ids.add(file_id)
        key = f"{clause_name}_{file_id}"
        if key not in result:
            result[key] = {
                "clause_name": clause_name,
                "file_id": str(file_id),
                "groups": {g_id: {"id": g_id, "name": g_name}}
            }
        else:
            result[key]["groups"][g_id] = {"id": g_id, "name": g_name}

    # 第二步：获取这些条款的完整组信息
    if file_ids:
        group_dict = db_get_groups_by_clause_ids(list(file_ids))
        for item in result.values():
            item["groups"] = group_dict.get(item["file_id"], [])

    return list(result.values())


# 普通用户获取有权限的条款名称
@with_session
def db_get_normal_user_clause_names(session, user_id: int) -> List[Dict]:
    """普通用户获取有权限的条款名称"""
    query = session.query(ClauseStandard.clause_name, ClauseFile.id)\
        .join(ClauseFile, ClauseStandard.file_id == ClauseFile.id)\
        .join(ClauseGroups, ClauseGroups.clause == ClauseFile.id)\
        .join(UserGroup, ClauseGroups.group == UserGroup.group)\
        .filter(ClauseStandard.is_delete.is_(False), UserGroup.user == user_id, ClauseFile.is_delete.is_(False))\
        .distinct()\
        .all()

    return [{"clause_name": clause_name, "file_id": str(file_id)} for clause_name, file_id in query]


# 新增条款文件内容
@with_session
def db_create_standard_clause(session, clause_metadata, user_id, file_id):
    clause = ClauseStandard(
        clause_name=clause_metadata.get("clause_name"),
        line_number=clause_metadata.get("line_number"),
        institution=clause_metadata.get("institution"),
        clause_number=clause_metadata.get("clause_number"),
        original_text=clause_metadata.get("original_text"),
        artificial_text=clause_metadata.get("artificial_text"),
        area=clause_metadata.get("area"),
        cargo=clause_metadata.get("cargo"),
        user=user_id,
        file_id=file_id,
    )
    session.add(clause)
    session.commit()
    return clause


@with_session
def db_delete_clauses(session, file_id):
    if isinstance(file_id, str):
        file_id = uuid.UUID(file_id)
    clause_list = session.query(ClauseStandard)\
        .filter(ClauseStandard.is_delete.is_(False), ClauseStandard.file_id == file_id)\
        .delete()
    session.commit()
    return True


@with_session
def db_delete_clause(session, clause_id):
    if isinstance(clause_id, str):
        clause_id = uuid.UUID(clause_id)
    session.query(ClauseStandard).get(clause_id).delete()
    return True


@with_session
def db_update_standard_clause_content(session, clause: ClauseStandard, update_data: dict) -> ClauseStandard:
    """更新条款信息"""
    for field, value in update_data.items():
        if value is not None:
            setattr(clause, field, value)
    session.add(clause)
    session.commit()
    return clause


""" ================================== ClauseFile ======================================"""


# 根据条款ID获取完整组信息
@with_session
def db_get_groups_by_clause_ids(session, clause_ids: List[int]) -> Dict[str, List[Dict]]:
    """根据条款ID获取完整组信息"""
    result = defaultdict(list)

    query = session.query(ClauseFile.id, Group.id, Group.name)\
        .join(ClauseGroups, ClauseGroups.clause == ClauseFile.id)\
        .join(Group, ClauseGroups.group == Group.id)\
        .filter(ClauseFile.id.in_(clause_ids))\
        .all()

    for file_id, g_id, g_name in query:
        result[str(file_id)].append({"id": g_id, "name": g_name})

    return dict(result)


# 获取所有 对比条款文件或者所有标准条款文件
@with_session
def db_get_all_clause_files(session, clause_type):
    return session.query(ClauseFile)\
        .filter(ClauseFile.is_delete.is_(False), ClauseFile.clause_type == clause_type)\
        .all()


# 获取用户的所有对比条款文件或者所有标准条款文件
@with_session
def db_get_clause_files_by_userId(session, user_id, clause_type):
    return session.query(ClauseFile)\
        .filter(ClauseFile.created_by == user_id, ClauseFile.clause_type == clause_type, ClauseFile.is_delete.is_(False))\
        .all()


# 获取指定id的条款文件
@with_session
def db_get_clause_file_by_fileId(session, file_id, clause_type=None):
    if isinstance(file_id, str):
        file_id = uuid.UUID(file_id)
    if clause_type is None:
        return session.query(ClauseFile)\
                .get(file_id)
    else:
        return session.query(ClauseFile)\
                .filter(ClauseFile.id == file_id, ClauseFile.clause_type == clause_type, ClauseFile.is_delete.is_(False))\
                .first()


# 获取指定文件名的条款文件
@with_session
def db_get_clause_file_by_filename(session, user_id, file_name, clause_type):
    return session.query(ClauseFile)\
        .filter(ClauseFile.created_by == user_id, ClauseFile.clause_type == clause_type, ClauseFile.name == file_name)\
        .first()


@with_session
def db_get_clause_file_by_filter(session, user_id, file_id):
    return session.query(ClauseFile)\
        .join(ClauseGroups, ClauseFile.id == ClauseGroups.clause)\
        .join(Group, ClauseGroups.group == Group.id)\
        .join(UserGroup, UserGroup.group == Group.id)\
        .filter(ClauseFile.is_delete.is_(False), UserGroup.user == user_id, ClauseFile.id == file_id)\
        .first()


# 更新条款文件的删除状态，delete=True是删除，False是恢复
@with_session
def db_update_clause_file_delete_state(session, clause_id, delete):
    clause = session.query(ClauseFile).get(clause_id)
    if not clause:
        return None
    clause.is_delete = delete
    session.commit()
    return clause


# 更新条款文件的解析状态，=1是完成解析，=0是未完成解析
@with_session
def db_update_clause_file_parse_state(session, clause_id):
    if isinstance(clause_id, str):
        clause_id = uuid.UUID(clause_id)
    clause = session.query(ClauseFile).get(clause_id)
    if not clause:
        return None
    clause.status = 1
    session.commit()
    return clause


# 新增条款文件
@with_session
def db_create_clause_file(session, user_id, file_name, origin_name,
                     location, file_type, file_size, clause_type
                     ):
    clause = ClauseFile(created_by=user_id, name=file_name, origin_name=origin_name, location=location,
               type=file_type, size=file_size, clause_type=clause_type)
    session.add(clause)
    session.commit()
    return clause


""" ================================== ClauseCompareHistory ======================================"""


# 获取所有条款对比记录
@with_session
def db_get_comp_his_by_filter(session, user, page, size):
    if user.check_superman_role():
        query = session\
            .query(ClauseCompareHistory.id)\
            .filter(ClauseCompareHistory.is_delete.is_(False))
    elif user.check_admin_role():
        # 获取当前用户所在的所有组ID
        subquery_groups = session\
            .query(UserGroup.group)\
            .filter(UserGroup.user == user.id)

        # 获取这些组中的所有用户ID
        subquery_users = session\
            .query(UserGroup.user)\
            .filter(UserGroup.group.in_(subquery_groups))

        # 查询符合条件的ClauseHis记录
        query = session\
            .query(ClauseCompareHistory.id)\
            .filter(ClauseCompareHistory.created_by.in_(subquery_users),ClauseCompareHistory.is_delete.is_(False))
    else:
        query = session\
            .query(ClauseCompareHistory.id)\
            .filter(ClauseCompareHistory.created_by == user.id, ClauseCompareHistory.is_delete.is_(False))

    # 先查询需要的数据 ID，再加载详细信息
    subquery = (
        query
        .order_by(desc(ClauseCompareHistory.updated_time))
        .offset((page - 1) * size)
        .limit(size)
        .subquery()
    )

    clause_comp_history = session.query(ClauseCompareHistory).filter(ClauseCompareHistory.id.in_(subquery)).all()

    total = query.count()
    return total, clause_comp_history


@with_session
def db_get_clause_compare_his_by_id(session, his_id, user_id=None):
    if isinstance(his_id, str):
        his_id = uuid.UUID(his_id)
    if user_id is None:
        return session.query(ClauseCompareHistory)\
                .get(his_id)
    else:
        return session.query(ClauseCompareHistory)\
                .filter(ClauseCompareHistory.id == his_id, ClauseCompareHistory.created_by == user_id)\
                .first()


@with_session
def db_delete_clause_compare_his_by_id(session, his_id):
    if isinstance(his_id, str):
        his_id = uuid.UUID(his_id)
    session.query(ClauseCompareHistory).get(his_id).delete()
    return True


@with_session
def db_save_compare_results(session, his_id, clause_file: ClauseFile, parsed_docx, compare_result, user_id):
    his = ClauseCompareHistory(
        id=his_id,
        name=clause_file.origin_name,
        file_hash_name=clause_file.name,
        parsed_docs=parsed_docx,
        created_by=user_id,
        clause=clause_file.id,
        compare_result=compare_result
    )
    session.add(his)
    session.commit()
    return his


""" ================================== ClauseGroups ======================================"""


# 创建用户组权限
@with_session
def db_create_clause_group(session, clause_id, user_group_id):
    cg = ClauseGroups(clause=clause_id, group=user_group_id)
    session.add(cg)
    session.commit()
    return cg


@with_session
def db_delete_clause_groups(session, file_id):
    cgs = session.query(ClauseGroups)\
        .filter(ClauseGroups.clause == file_id)\
        .delete()
    return True


@with_session
def db_get_current_clause_groups(session, clause_file_id: str) -> List[int]:
    """获取条款当前关联的组ID列表"""
    cg_list = session.query(ClauseGroups)\
        .filter(ClauseGroups.clause == clause_file_id)\
        .all()
    return [g.group for g in cg_list]


@with_session
def db_update_clause_groups(session, clause_file_id: str, new_group_ids: List[int]) -> None:
    """更新条款关联的组"""
    # 删除不再需要的关联
    session.query(ClauseGroups) \
        .filter(ClauseGroups.clause == clause_file_id) \
        .filter(ClauseGroups.group.notin_(new_group_ids)) \
        .delete(synchronize_session=False)

    # 添加新的关联
    current_groups = {g.group for g in session.query(ClauseGroups).filter(ClauseGroups.clause == clause_file_id).all()}

    for group_id in new_group_ids:
        if group_id not in current_groups:
            session.add(ClauseGroups(group=group_id, clause=clause_file_id))

    session.commit()
